package edu.uwm.capstone.diasmart.database;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Locale;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import edu.uwm.capstone.diasmart.R;
import edu.uwm.capstone.util.DebugUtil;

public class DatabaseHelper extends SQLiteOpenHelper implements
		DatabaseChangeListener {

	private static final String DATABASE_NAME = "DiasmartDB";
	private static final int DATABASE_VERSION = 1;
	private static final String TAG = "Diasmart/DatabaseHelper";
	private static final ArrayList<DatabaseChangeListener> listeners = new ArrayList<DatabaseChangeListener>();

	public static final String FIELD_CATEGORIES = "categories";
	public static final String FIELD_CATEGORY = "category";
	public static final String FIELD_CATEGORY_ID = "category_id";
	public static final String FIELD_END_TIME = "end_time";
	public static final String FIELD_ENTRY_TIME = "entry_time";
	public static final String FIELD_ID = "_id";
	public static final String FIELD_IS_DEFAULT = "is_default";
	public static final String FIELD_LAST_CATEGORY_ID = "last_category_id";
	public static final String FIELD_NOTE = "note";
	public static final String FIELD_PERIOD_TIME = "period_time";
	public static final String FIELD_PERIOD_TYPE = "period_type";
	public static final String FIELD_PERSISTENT = "persistent";
	public static final String FIELD_REMINDER = "reminder";
	public static final String FIELD_REMINDER_TEXT = "reminder_text";
	public static final String FIELD_START_TIME = "start_time";
	public static final String FIELD_SUB_TYPE = "sub_type";
	public static final String FIELD_SUB_TYPE_ID = "sub_type_id";
	public static final String FIELD_TYPES = "types";
	public static final String FIELD_TYPE_ID = "type_id";
	public static final String FIELD_UNIT = "unit";
	public static final String FIELD_USER_DEFAULT = "user_default";
	public static final String FIELD_USER_ORDER = "user_order";
	public static final String FIELD_VALUE = "value";
	public static final String[] TABLES = { "categories", "sub_types",
			"entries", "reminders" };
	public static final String TABLE_CATEGORIES = "categories";
	public static final String TABLE_ENTRIES = "entries";
	public static final String TABLE_REMINDERS = "reminders";
	public static final String TABLE_SUB_TYPES = "sub_types";

	private CategoryDAO categoryDAO = null;
	private Context context;
	private EntryDAO entryDAO = null;
	private ReminderDAO reminderDAO = null;
	private ReportingDAO reportingDAO = null;
	private SubTypeDAO subTypeDAO = null;

	public DatabaseHelper(Context paramContext) {
		super(paramContext, DATABASE_NAME, null, DATABASE_VERSION);
		addDatabaseChangeListener(this);
		this.context = paramContext;
	}

	public static void addDatabaseChangeListener(
			DatabaseChangeListener paramDatabaseChangeListener) {
		listeners.add(paramDatabaseChangeListener);
	}

	public DatabaseHelper(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);

	}

	@Override
	public void onTableChanged(String tableName) {
		if ("categories".equals(tableName))
			this.categoryDAO = null;
		if ("sub_types".equals(tableName))
			this.subTypeDAO = null;
		if ("reminders".equals(tableName))
			this.reminderDAO = null;

	}

	@Override
	public void onTableRowChanged(String tableName, long rowId) {
		onTableChanged(tableName);

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.execSQL("CREATE TABLE categories (_id INTEGER PRIMARY KEY AUTOINCREMENT,category VARCHAR NOT NULL,is_default INTEGER DEFAULT 0);");
			db.execSQL("CREATE TABLE entries (_id INTEGER PRIMARY KEY AUTOINCREMENT,entry_time INTEGER NOT NULL,type_id INTEGER NOT NULL, category_id INTEGER NOT NULL, sub_type_id INTEGER NOT NULL, note VARCHAR, value INTEGER NOT NULL);");
			db.execSQL("CREATE INDEX IDX_entries_category_id ON entries (category_id)");
			db.execSQL("CREATE INDEX IDX_entries_statistics_type_id ON entries (type_id)");
			db.execSQL("CREATE TABLE sub_types(_id INTEGER PRIMARY KEY AUTOINCREMENT, type_id INTEGER NOT NULL, sub_type VARCHAR NOT NULL, is_default INTEGER DEFAULT 0);");
			db.execSQL("ALTER TABLE categories ADD user_order INTEGER");
			db.execSQL("ALTER TABLE categories ADD start_time INTEGER");
			db.execSQL("ALTER TABLE categories ADD end_time INTEGER");
			db.execSQL("ALTER TABLE categories ADD last_category_id INTEGER");
			db.execSQL("ALTER TABLE categories ADD user_default INTEGER");
			db.execSQL("ALTER TABLE sub_types ADD unit VARCHAR");
			db.execSQL("ALTER TABLE sub_types ADD user_order INTEGER");
			db.execSQL("ALTER TABLE sub_types ADD user_default INTEGER");
			db.execSQL("UPDATE categories SET start_time=-1, end_time=-1");
			db.execSQL("CREATE TABLE reminders(_id INTEGER PRIMARY KEY AUTOINCREMENT, reminder VARCHAR NOT NULL, reminder_text VARCHAR NOT NULL, period_time INTEGER DEFAULT 0, period_type INTEGER DEFAULT 0, user_order INTEGER DEFAULT 0, categories VARCHAR, types VARCHAR);");
			db.execSQL("ALTER TABLE reminders ADD persistent INTEGER DEFAULT 0");			
			DebugUtil
					.debug(this,
							"Creating reminders:\n"
									+ "CREATE TABLE reminders(_id INTEGER PRIMARY KEY AUTOINCREMENT, reminder VARCHAR NOT NULL, reminder_text VARCHAR NOT NULL, period_time INTEGER DEFAULT 0, period_type INTEGER DEFAULT 0, user_order INTEGER DEFAULT 0, categories VARCHAR, types VARCHAR);");
			
			insertDefaultReminder(db);
			DebugUtil.debug(this,"Created DefaultReminder");
			
			insertSystemData(db);

		} catch (Exception dbException) {
			Log.e(TAG, "Unexpected exception occurred when creating database: "
					+ dbException.getMessage(), dbException);
		}

	}

	private void insertSystemData(SQLiteDatabase db) {

		db.beginTransaction();
		try {
			Category[] arrayOfCategory = SystemData
					.getSystemCategories(this.context);

			for (int i = 0; i < arrayOfCategory.length; i++) {

				getCategoryDAO().insert(db, arrayOfCategory[i]);
				arrayOfCategory[i].setModified(true);
			}
			SubType[] arrayOfSubType = SystemData
					.getSystemSubTypes(this.context);
			for (int j = 0; j < arrayOfSubType.length; j++) {
				getSubTypeDAO().insert(db, arrayOfSubType[j]);
				arrayOfSubType[j].setModified(true);

			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			DebugUtil.debug(TAG, "Exception" + e.getLocalizedMessage() + ","
					+ e);
		} finally {
			db.endTransaction();
		}

	}

	private void insertDefaultReminder(SQLiteDatabase db) {
		Reminder localReminder = new Reminder(-1L,
				this.context.getString(R.string.glucose_reminder_name),
				this.context.getString(R.string.glucose_reminder_text), 2, 1,
				0, null, null, false);
		localReminder.setModified(true);		
		getReminderDAO().insert(db, localReminder);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Not to worry right now

	}

	public void refresh() {
		this.categoryDAO = null;
		this.entryDAO = null;
		this.subTypeDAO = null;
		this.reminderDAO = null;
		this.reportingDAO = null;
	}

	public CategoryDAO getCategoryDAO() {
		if (this.categoryDAO == null)
			this.categoryDAO = new CategoryDAO(this);
		return this.categoryDAO;
	}

	public EntryDAO getEntryDAO() {
		if (this.entryDAO == null)
			this.entryDAO = new EntryDAO(this);
		return this.entryDAO;
	}

	public ReminderDAO getReminderDAO() {
		if (this.reminderDAO == null)
			this.reminderDAO = new ReminderDAO(this);
		return this.reminderDAO;
	}

	public ReportingDAO getReportingDAO() {
		if (this.reportingDAO == null)
			this.reportingDAO = new ReportingDAO(this);
		return this.reportingDAO;
	}

	public SubTypeDAO getSubTypeDAO() {
		if (this.subTypeDAO == null)
			this.subTypeDAO = new SubTypeDAO(this);
		return this.subTypeDAO;
	}

	public Context getContext() {
		return this.context;
	}

	public static Date toDate(String paramString) {
		SimpleDateFormat localSimpleDateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss", Locale.US);
		try {
			Date localDate = localSimpleDateFormat.parse(paramString);
			return localDate;
		} catch (ParseException localParseException) {
			Log.e(TAG, "Unable to parse date '" + paramString + "'",
					localParseException);
			throw new RuntimeException(localParseException);
		}
	}

	public static String toSQLDateString(Date defaultDate) {
		return new SimpleDateFormat("yyyy-MM-dd", Locale.US)
				.format(defaultDate);
	}

	public static String toSQLTimestampString(Date defaultTime) {
		return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.US)
				.format(defaultTime);
	}

	public static void fireTableChanged(String tableName) {
		for (int i = 0; i < listeners.size(); i++) {
			((DatabaseChangeListener) listeners.get(i))
					.onTableChanged(tableName);
		}
	}

	public static void fireTableRowChanged(String tableName, long rowId) {
		for (int i = 0; i < listeners.size(); i++) {
			((DatabaseChangeListener) listeners.get(i)).onTableRowChanged(
					tableName, rowId);
		}
	}

	public static void closeDatabaseHelper(DatabaseHelper db) {
		if (db != null)
			db.close();
	}

	public static void removeDatabaseChangeListener(
			DatabaseChangeListener paramDatabaseChangeListener) {
		listeners.remove(paramDatabaseChangeListener);
	}

	public static void close(DatabaseHelper paramDatabaseHelper,
			Cursor paramCursor) {

		if (paramCursor != null) {
			try {
				paramCursor.close();

			} catch (Exception e) {
				Log.e(TAG, "Unexpected error closing cursor", e);
			}
		}
		if (paramDatabaseHelper != null) {
			try {
				paramDatabaseHelper.close();

			} catch (Exception e) {
				Log.e(TAG, "Unexpected error closing DatabaseHelper", e);
			}
		}

	}

	public void close() {
		try {

			removeDatabaseChangeListener(this);
			this.entryDAO = null;
			this.categoryDAO = null;
			this.subTypeDAO = null;
			this.reminderDAO = null;
			super.close();

		} catch (Exception e) {
			Log.e(TAG, "Exception occured during closing the database helper"
					+ e.getLocalizedMessage());
		} finally {
			Log.d(TAG, "DatabaseHelper Closed.");

		}

	}

	/*
	 * private void insertTestData(SQLiteDatabase paramSQLiteDatabase) {
	 * EntryContext localEntryContext = new EntryContext(this.context);
	 * paramSQLiteDatabase.beginTransaction(); try { BPType localBPType =
	 * (BPType)EntryTypeFactory.getInstance(5); Calendar localCalendar =
	 * Calendar.getInstance(); DateUtil.trimTime(localCalendar); Random
	 * localRandom = new Random(System.currentTimeMillis()); for (int i = 0; ;
	 * i++) { if (i >= 90) { paramSQLiteDatabase.setTransactionSuccessful();
	 * return; } Entry localEntry1 = new Entry(-1L, 25200000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 4, 6L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 190 +
	 * localRandom.nextInt(3)); getEntryDAO().insert(paramSQLiteDatabase,
	 * localEntryContext, localEntry1); Entry localEntry2 = new Entry(-1L,
	 * 25200000L + (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 6, 6L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 70 +
	 * localRandom.nextInt(3)); getEntryDAO().insert(paramSQLiteDatabase,
	 * localEntryContext, localEntry2); Entry localEntry3 = new Entry(-1L,
	 * 25200000L + (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 7, 6L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 2 +
	 * localRandom.nextInt(3)); getEntryDAO().insert(paramSQLiteDatabase,
	 * localEntryContext, localEntry3); short s1 = (short)(110 +
	 * localRandom.nextInt(10)); short s2 = (short)(70 +
	 * localRandom.nextInt(10)); Entry localEntry4 = new Entry(-1L, 25200000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 5, 6L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "",
	 * localBPType.toIntValue(s1, s2));
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry4); Entry localEntry5 = new Entry(-1L, 25200000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 3, 6L, 2L, "", 3 + localRandom.nextInt(10));
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry5); Entry localEntry6 = new Entry(-1L, 28800000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 0, 2L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 3 +
	 * localRandom.nextInt(8) + localRandom.nextDouble());
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry6); Entry localEntry7 = new Entry(-1L, 28800000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 1, 2L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 15 +
	 * localRandom.nextInt(8) + localRandom.nextDouble());
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry7); Entry localEntry8 = new Entry(-1L, 28800000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 3, 6L, 3L, "", 3 + localRandom.nextInt(5));
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry8); Entry localEntry9 = new Entry(-1L, 39600000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 0, 4L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 3 +
	 * localRandom.nextInt(8) + localRandom.nextDouble());
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry9); Entry localEntry10 = new Entry(-1L, 61200000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 0, 6L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 3 +
	 * localRandom.nextInt(8) + localRandom.nextDouble());
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry10); Entry localEntry11 = new Entry(-1L, 61200000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 1, 6L,
	 * SystemData.getSystemUnfiledSubType(this.context).getId(), "", 15 +
	 * localRandom.nextInt(8) + localRandom.nextDouble());
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry11); Entry localEntry12 = new Entry(-1L, 79200000L +
	 * (localCalendar.getTimeInMillis() + 1000 * (60 *
	 * localRandom.nextInt(15))), 3, 6L, 2L, "", 3 + localRandom.nextInt(10));
	 * getEntryDAO().insert(paramSQLiteDatabase, localEntryContext,
	 * localEntry12); localCalendar.add(6, -1); } } finally {
	 * paramSQLiteDatabase.endTransaction(); } }
	 */
}
